package com.cdie.myschool.service.impl;

import cn.hutool.core.util.StrUtil;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.cdie.myschool.common.R;
import com.cdie.myschool.domain.dto.StudentDTO;
import com.cdie.myschool.domain.dto.StudentDepartmentDTO;
import com.cdie.myschool.domain.entity.Calendar;
import com.cdie.myschool.domain.entity.*;
import com.cdie.myschool.domain.vo.*;
import com.cdie.myschool.mapper.CalendarMapper;
import com.cdie.myschool.mapper.StudentTermReportMapper.*;
import com.cdie.myschool.service.StudentTermReportService;
import com.cdie.myschool.util.CalendarUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.cdie.myschool.domain.vo.ScoreAnalysisVO;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Logger;

/**
 * @author 明祺奇-199000331
 * @date 2022/10/5 - 9:41
 */
@Service
public class StudentTermReportServiceImpl implements StudentTermReportService {
    @Autowired
    private CalendarMapper calendarMapper;
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentDepartmentMapper studentDepartmentMapper;
    @Autowired
    private StudentCourseMapper studentCourseMapper;
    @Autowired
    private CalendarUtil calendarUtil;
    @Autowired
    private ScholarshipMapper scholarshipMapper;
    @Autowired
    private QualityEducationMapper qualityEducationMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private BookHistoryMapper bookHistoryMapper;
    @Autowired
    private LibraryMapper libraryMapper;
    @Autowired
    private ScoreMapper scoreMapper;
    @Autowired
    private CourseMapper courseMapper;


    /**
     * 功能描述:根据传入的学号查找并返回姓名和入学时长到启动页
     * @Param: [studentDTO]
     * @Return: com.cdie.myschool.common.R
     * @Author: 朱华扬
     * @Date: 2022/10/10 8:41
     */
    @Override
    @DS("master")
    public R startupPage(StudentDTO studentDTO) {
        //创建查询条件构造器，以学号做查询条件
        LambdaQueryWrapper<Student> studentId = new LambdaQueryWrapper<>();
        studentId.eq(Student::getStudentCode,studentDTO.getStudentCode());
        //根据学号从学生表查找入学时间
        Date day = studentMapper.selectOne(studentId).getAdmissionDate();
        //根据当前时间和入学时间计算入学时长（天数）
        long targetTime = day.getTime();
        long todayTime = new Date().getTime();
        long LengthOfEnrollment =Math.abs(targetTime-todayTime)/1000/60/60/24;
        StudentBaseInfoVO studentBaseInfoVO = new StudentBaseInfoVO();
        //根据学号查找学生姓名并将学生姓名和入学时长赋值到studentBaseInfoVO中返回给前端
        studentBaseInfoVO.setStudent_name(studentMapper.selectOne(studentId).getStudentName());
        studentBaseInfoVO.setLengthOfEnrollment(LengthOfEnrollment);
        return R.Success(studentBaseInfoVO);
    }

    /**
     * 功能描述: 根据传入学号查找并返回入学时间、学院、专业、班级、学号到院系信息页面
     * @Param: [studentDTO]
     * @Return: com.cdie.myschool.common.R
     * @Author: 朱华扬
     * @Date: 2022/10/11 8:51
     */
    @Override
    @DS("master")
    public R departmentInformation(StudentDTO studentDTO) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //创建查询条件构造器，以学号做查询条件
        LambdaQueryWrapper<Student> studentId = new LambdaQueryWrapper<>();
        studentId.eq(Student::getStudentCode,studentDTO.getStudentCode());
        //根据学号查找学生信息
        Student student = studentMapper.selectOne(studentId);
        //根据学号查找学生学院名称、专业名称、班级名称
        StudentDepartmentDTO studentDepartmentDTO = studentDepartmentMapper.departmentInformation(student.getStudentCode());
        String studentBaseInfo = studentDepartmentDTO.getDepartmentName()+"-"+studentDepartmentDTO.getMajorName()+"-"+studentDepartmentDTO.getClassName();
        StudentDepartmentVO studentDepartmentVO = new StudentDepartmentVO();
        studentDepartmentVO.setStudentBaseInfo(studentBaseInfo);
        //将学生信息中的入学时间和学号赋值到studentDepartmentVO中
        studentDepartmentVO.setAdmissionDate(sdf.format(student.getAdmissionDate()));
        studentDepartmentVO.setStudentCode(student.getStudentCode());
        return R.Success(studentDepartmentVO);
    }

    /**
     * 功能描述: 根据传入学号查找并返回学期名、本学期课程数量、累计已修课程数量、本学期课程名称
     * @Param: [studentDTO]
     * @Return: com.cdie.myschool.common.R
     * @Author: 朱华扬
     * @Date: 2022/10/11 8:51
     */
    @Override
    @DS("master")
    public R courseInformation(StudentDTO studentDTO) {
        int semesterId = 0;
        Date nowTime = new Date();
        StudentCourseVO studentCourseVO = new StudentCourseVO();
        //查找出所有学期的开始时间和结束时间
        List<Calendar> calendarList = calendarMapper.selectList(null);
        //循环获取每学期的开始时间和结束时间
        for (Calendar calendar : calendarList) {
            //通过calendarUtil工具类判断当前时间属于哪个学期
            boolean res = calendarUtil.belongCalendar(nowTime,calendar.getBeginTime(),calendar.getEndTime());
            if (res){
                //获取当前学期id
                semesterId = calendar.getSemesterId();
                //创建查询条件构造器，以学期id做查询条件
                LambdaQueryWrapper<Calendar> qw = new LambdaQueryWrapper<>();
                //根据学期id查找学年及学期，并赋值给studentCourseVO
                Calendar calendar1 = calendarMapper.selectOne(qw.eq(Calendar::getSemesterId,semesterId));
                String schoolYear = calendar1.getSchoolYear()+"-"+(Integer.parseInt(calendar1.getSchoolYear().substring(0,calendar1.getSchoolYear().length()))+1)+"学年";
                String semester="";
                if (calendar1.getSemester().equals("0")){
                    semester = "第一学期";
                }else if(calendar1.getSemester().equals("1")){
                    semester = "第二学期";
                }
                studentCourseVO.setSemester(schoolYear+semester);
            }
        }
        //根据学生学号和学期id查找学生本学期课程数量
        int courseNumber = studentCourseMapper.selectCourseNumber(studentDTO.getStudentCode(),semesterId);
        studentCourseVO.setCourseNumber(courseNumber);
        //根据学生学号查找学生累计已修课程数量
        int takenCoursesNumber = studentCourseMapper.selectTakenCoursesNumber(studentDTO.getStudentCode());
        studentCourseVO.setTakenCoursesNumber(takenCoursesNumber);
        //根据学生学号和学期id查找本学期课程名称
        List<String> courseNameList = studentCourseMapper.selectCourseNameList(studentDTO.getStudentCode(),semesterId);
        studentCourseVO.setCourseNameList(courseNameList);
        return R.Success(studentCourseVO);
    }

    /**
     * 功能描述: 根据传入学号查找本学期奖学金内容和本学期素质教育积分
     * @Param: [studentDTO]
     * @Return: com.cdie.myschool.common.R
     * @Author: 朱华扬
     * @Date: 2022/10/13 9:10
     */
    @Override
    public R studentEngineeringData(StudentDTO studentDTO) {
        int semesterId = 0;
        Date nowTime = new Date();
        //查找出所有学期的开始时间和结束时间
        List<Calendar> calendarList = calendarMapper.selectList(null);
        //循环获取每学期的开始时间和结束时间
        for (Calendar calendar : calendarList) {
            //通过calendarUtil工具类判断当前时间属于哪个学期
            boolean res = calendarUtil.belongCalendar(nowTime,calendar.getBeginTime(),calendar.getEndTime());
            if (res){
                //获取当前学期id
                semesterId = calendar.getSemesterId();
                //semesterId = 31;
            }
        }
        //查找该学生本学期获得的奖学金名称
        Map<SFunction<Scholarship,?>, Object> map1 = new HashMap<>();
        map1.put(Scholarship::getSemesterId,semesterId);
        map1.put(Scholarship::getStudentCode,studentDTO.getStudentCode());
        LambdaQueryWrapper<Scholarship> qw1 = new LambdaQueryWrapper<>();
        StudentEngineeringDataVO studentEngineeringDataVO = new StudentEngineeringDataVO();
        List<Scholarship> scholarshipList = scholarshipMapper.selectList(qw1.allEq(map1));
        List<String> scholarshipName = new ArrayList<>();
        for (Scholarship scholarship : scholarshipList){
            scholarshipName.add(scholarship.getScholarshipName());
        }
        studentEngineeringDataVO.setScholarshipName(scholarshipName);


        //查找该学生本学期所获得的素质教育积分
        Map<SFunction<QualityEducation,?>, Object> map2 = new HashMap<>();
        map2.put(QualityEducation::getSemesterId,semesterId);
        map2.put(QualityEducation::getStudentCode,studentDTO.getStudentCode());
        LambdaQueryWrapper<QualityEducation> qw2 = new LambdaQueryWrapper<>();
        List<QualityEducation> qualityEducationList = qualityEducationMapper.selectList(qw2.allEq(map2));
        int score = 0;
        for (QualityEducation qualityEducation : qualityEducationList){
            score = score+qualityEducation.getScore();
        }
        studentEngineeringDataVO.setScore(score);
        return R.Success(studentEngineeringDataVO);
    }
    /**
     * @Description:导入借阅信息到借阅表中
     * @Param: [bookHistoryOldList]
     * @return: void
     * @Author: 毛康源
     * @Date: 2022/10/12
     */
    @Override
    @DS("master")
    public void insertBookHistoryDataInBookHistory(List<BookHistoryOld> bookHistoryOldList) {
        if(bookHistoryOldList.size()==0){
            return;
        }
        Logger logger = null;
        StringBuilder sql = new StringBuilder();
        sql.append("insert into book_borrowing_history(borrowing_id,book_id,reader_id,borrowing_date,should_return_date,return_date,timestamp) values");
        sql.append("('")
                .append(bookHistoryOldList.get(0).getDKLSID()).append("','")
                .append(bookHistoryOldList.get(0).getXMID()).append("','")
                .append(bookHistoryOldList.get(0).getDZH()).append("','")
                .append(bookHistoryOldList.get(0).getJCRQ()).append("','")
                .append(bookHistoryOldList.get(0).getYHRQ()).append("',NULLIF('")
                .append(bookHistoryOldList.get(0).getGHSJ()).append("','null'),'")
                .append(bookHistoryOldList.get(0).getTSTAMP().replace(" ", "")).append("')");
        ;
        for (int i = 1; i < bookHistoryOldList.size(); i++) {
            sql.append(",(")
                    .append(bookHistoryOldList.get(i).getDKLSID()).append("','")
                    .append(bookHistoryOldList.get(i).getXMID()).append("','")
                    .append(bookHistoryOldList.get(i).getDZH()).append("','")
                    .append(bookHistoryOldList.get(i).getJCRQ()).append("','")
                    .append(bookHistoryOldList.get(i).getYHRQ()).append("',NULLIF('")
                    .append(bookHistoryOldList.get(i).getGHSJ()).append("','null'),'")
                    .append(bookHistoryOldList.get(i).getTSTAMP().replace(" ", "")).append("')");
        }
        System.out.println(sql.toString());
        jdbcTemplate.batchUpdate(sql.toString());
    }
    @Override
    public R libraryData(StudentDTO studentDTO) {
        List<Library> libraries2=libraryMapper.getRankSum();
        LambdaQueryWrapper<Library> libraryLambdaQueryWrapper=new LambdaQueryWrapper<>();
        libraryLambdaQueryWrapper.and(i->i.eq(Library::getSemesterId,studentDTO.getSemesterId()).eq(Library::getUserId,studentDTO.getStudentCode()));
        List<Library> libraries=libraryMapper.selectList(libraryLambdaQueryWrapper);
        LibraryVO libraryVO=new LibraryVO();
        if(libraries.size()>0){
            Integer passCs=libraryMapper.selectCount(libraryLambdaQueryWrapper);
            Integer zgSc=libraryMapper.getSum(studentDTO.getStudentCode(),studentDTO.getSemesterId());
            libraryVO.setPassls(passCs);
            libraryVO.setStayTime(zgSc);
            LambdaQueryWrapper<BookHistory> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(BookHistory::getReaderId, studentDTO.getStudentCode());
            List<BookHistory> bookHistoryList = bookHistoryMapper.selectList(lambdaQueryWrapper);
            if (bookHistoryList.size() > 0) {
                if (bookHistoryList.get(0).getSemesterId() == null) {
                    List<BookHistory> bookHistoryList1 = bookHistoryMapper.selectList(lambdaQueryWrapper.select(BookHistory::getBorrowingDate));
                    List<Integer> calendars = new ArrayList<>();
                    bookHistoryList1.forEach(i -> {
                        Calendar calendar = bookHistoryMapper.getSemesterIdBook(i.getBorrowingDate());
                        calendars.add(calendar.getSemesterId());
                    });
                    for (int i = 0; i < bookHistoryList1.size(); i++) {
                        bookHistoryMapper.updateSemeterIdBook(calendars.get(i), studentDTO.getStudentCode(), bookHistoryList1.get(i).getBorrowingDate());
                    }
                    return R.Success("插入成功");
                }else{
                    Integer books=bookHistoryMapper.selectCount(lambdaQueryWrapper.eq(BookHistory::getSemesterId,studentDTO.getSemesterId()));
                    libraryVO.setBooks(books);

                    LambdaQueryWrapper<BookHistory> bookHistoryLambdaQueryWrapper=new LambdaQueryWrapper<>();
                    bookHistoryLambdaQueryWrapper.select(BookHistory::getReturnDate,BookHistory::getShouldReturnDate).isNotNull(BookHistory::getReturnDate).eq(BookHistory::getReaderId,studentDTO.getStudentCode());
                    List<BookHistory> bookHistoryLi=bookHistoryMapper.selectList(bookHistoryLambdaQueryWrapper);
                    //超出期限会重复查询
                    Integer li=0;
                    for (int i=0;i<bookHistoryLi.size();i++){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//日期格式化
                        String reStr = sdf.format(bookHistoryLi.get(i).getReturnDate());//将Date转换为String
                        String shStr = sdf.format(bookHistoryLi.get(i).getShouldReturnDate());
                        String str1= StrUtil.removeAll(reStr,'-',' ',':');
                        String ste2=StrUtil.removeAll(shStr,'-',' ',':');
                        long reTime=Long.parseLong(str1);
                        long shTime=Long.parseLong(ste2);
                        if(reTime>shTime){
                            li++;
                        }
                    }
                    libraryVO.setHistory(li);

                    LibraryVO library=libraryMapper.getRankUser(studentDTO.getStudentCode());
                    Double i=0.00;
                    i=library.getRownum()/(libraries2.size()*1.00);
                    libraryVO.setRanking(i*100+"%");
                    return R.Success(libraryVO);
                }
            }else{
                return R.Failed("借阅表无数据");
            }
        }else{
            return R.Failed("图书馆通行表无数据");
        }
    }

    /**
     * @author 明祺奇
     * 成绩分析
     */
    @Override
    @DS("master")
    public R scoreAnalysis(BigInteger studentId) {
        ScoreAnalysisVO scoreAnalysisVO = new ScoreAnalysisVO();
        LambdaQueryWrapper<Score> wrapper = new LambdaQueryWrapper();
        wrapper.eq(Score::getStudentId, studentId);
        if (scoreMapper.selectCount(wrapper) > 0) {
            List<Score> scoreList = scoreMapper.selectList(wrapper);
            double gradePoint = 0.0;
            int total = 0;
            int numberOfFailedCourses = 0;
            List<String> courseNames = new ArrayList<>();
            for (Score i:scoreList) {
                gradePoint += i.getGradePoint();
                total++;
                if (StringUtils.isNumeric(i.getAchievement())){
                    double achievemnet = Double.parseDouble(i.getAchievement());
                    if (achievemnet<60) {
                        numberOfFailedCourses++;
            courseNames.add(getCourseNameByCourseId(i.getCourseId()));
                    }
                } else if (i.getAchievement().equals("不合格")) {
                    numberOfFailedCourses++;
                    courseNames.add(getCourseNameByCourseId(i.getCourseId()));
                }
            }
            scoreAnalysisVO.setGradePoint(String.format("%.1f",gradePoint/total));
            scoreAnalysisVO.setFailedCourseName(courseNames);
            scoreAnalysisVO.setNumberOfFailedCourses(numberOfFailedCourses);
            return R.Success(scoreAnalysisVO);
        } else {
            return R.Failed();
        }

    }

    /**
     * @author 明祺奇
     * 成绩分析成绩表
     */
    @Override
    public R scoreAnalysisScoreInfo(BigInteger studentId) {

        ScoreAnalysisVO scoreAnalysisVO = new ScoreAnalysisVO();
        Map<String,List<ScoreInfoVO>> map = new HashMap<String,List<ScoreInfoVO>>();

        List<Score> scoreList1 = scoreMapper.selectList(new QueryWrapper<Score>()
                .select("DISTINCT semester_id").lambda()
                .isNotNull(Score::getSemesterId)
        );
        for (Score i: scoreList1) {

            List<ScoreInfoVO> scoreInfoVOList = new LinkedList<>();
            List<Score> score = scoreMapper.selectList(new LambdaQueryWrapper<Score>().eq(Score::getSemesterId, i.getSemesterId()));
            for (Score j: score){
                ScoreInfoVO scoreInfoVO = new ScoreInfoVO();
                BeanUtils.copyProperties(j,scoreInfoVO);
                Course k = courseMapper.selectOne(new LambdaQueryWrapper<Course>()
                        .eq(Course::getCourseCode, j.getCourseId())
                        .select(Course::getCourseName));
                if (k == null){
//                    scoreInfoVO.setCourseName("数据库中查询不到该课程编号："+ j.getCourseId());
                    scoreInfoVO.setCourseName("数据库中查询不到该课程编号");
                } else {
                    scoreInfoVO.setCourseName(k.getCourseName());
                }
                scoreInfoVOList.add(scoreInfoVO);
            }
            String year = calendarMapper.selectOne(new LambdaQueryWrapper<Calendar>()
                    .eq(Calendar::getSemesterId, i.getSemesterId())).getSchoolYear();
            String semester = calendarMapper.selectOne(new LambdaQueryWrapper<Calendar>()
                    .eq(Calendar::getSemesterId, i.getSemesterId())).getSemester().equals("0")?"一":"二";
            map.put(year+"-"+(Integer.valueOf(year)+1)+"年第"+semester+"学期"
                    ,scoreInfoVOList);
        }
        scoreAnalysisVO.setScoreInfo(map);
        return R.Success(scoreAnalysisVO);
    }

    /**
     * @author 明祺奇
     * 根据课程id查询course表中课程名
     */
    @DS("master")
    public String getCourseNameByCourseId(String courseId) {
        LambdaQueryWrapper<Course> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.likeLeft(Course::getCourseCode, courseId);
        return courseMapper.selectOne(queryWrapper).getCourseName();
    }
}
